package fr.esisar.picaso.requetes;

import fr.esisar.picaso.entites.Livre;
import fr.esisar.picaso.entites.Photo;
import fr.esisar.picaso.moteur.Connexion;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class RequetesLivre {

	public RequetesLivre() {

	}

	public Livre getLivre(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT * FROM U0.Album WHERE idAlbum="+id;
		ResultSet rs = st.executeQuery(requete);

		Livre livre = null;
		while(rs.next()) {
			livre = new Livre(id, rs.getString("Titre"), rs.getString("sousTitre"), 
					rs.getInt("estpublique")==1, getPhotos(id), rs.getInt("idPhoto"), 
					rs.getString("preface"), rs.getString("postface"));
		}

		return livre;
	}

	public String getTitre(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT Titre FROM U0.Album WHERE idAlbum="+id;
		ResultSet rs = st.executeQuery(requete);

		String retour = null;
		while(rs.next()) {
			retour = new String(rs.getString("Titre"));
		}
		return retour;
	}
	public String getSousTitre(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT Sous_Titre FROM U0.Album WHERE idAlbum="+id;
		ResultSet rs = st.executeQuery(requete);

		String retour = null;
		while(rs.next()) {
			retour = new String(rs.getString("sousTitre"));
		}
		return retour;
	}
	public boolean getPublic(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT Titre FROM U0.Album WHERE idAlbum="+id;
		ResultSet rs = st.executeQuery(requete);

		boolean retour=false;
		while(rs.next()) {
			retour = rs.getInt("estpublique")==1;
		}
		return retour;
	}

	public int getIdPhoto(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT idPhoto FROM U0.Album WHERE idAlbum="+id;
		ResultSet rs = st.executeQuery(requete);

		int retour=0;
		while(rs.next()) {
			retour = rs.getInt("idPhoto");
		}
		return retour;
	}
	
	public Photo getPhoto(int id) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT * FROM u0.Photo WHERE idPhoto="+id; 

		ResultSet rs = st.executeQuery(requete);

		Photo p = null;
		while(rs.next()) {
			p = new Photo(rs.getInt("idPhoto"),rs.getString("Chemin"), rs.getString("AppareilPhoto") , 
					rs.getString("Objectif"), rs.getFloat("Focale"),rs.getString("Sensibilite"),rs.getFloat("Ouverture"), 
					rs.getFloat("vitesseObturation"), rs.getInt("Longueur"), rs.getInt("Largeur") , null);
			p.setTitre("Couverture");
			p.setCommentaire("Photo de couverture");
		}
		return p;
	}
	
	public ArrayList<Photo> getPhotos(int id) throws SQLException{
		ArrayList<Photo> listePhotos = new ArrayList<Photo>();

		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT * FROM u0.Photo, u0.Reference WHERE idAlbum="+id+
		" AND U0.Photo.idPhoto = u0.Reference.idPhoto ORDER BY ordre ASC"; 

		ResultSet rs = st.executeQuery(requete);

		while(rs.next()) {
			Photo p = new Photo(rs.getInt("idPhoto"),rs.getString("Chemin"), rs.getString("AppareilPhoto") , 
					rs.getString("Objectif"), rs.getFloat("Focale"),rs.getString("Sensibilite"),rs.getFloat("Ouverture"), 
					rs.getFloat("vitesseObturation"), rs.getInt("Longueur"), rs.getInt("Largeur") , null);
			p.setTitre(rs.getString("titre"));
			p.setCommentaire(rs.getString("commentaire"));
			listePhotos.add(p);
		}
		return listePhotos;
	}

	public ArrayList<Livre> getLivreClient(int idClient) throws SQLException{
		//TODO
		ArrayList<Livre> listeAlbums = new ArrayList<Livre>();

		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT * FROM U0.Album"
			+ " WHERE idAlbum IN ( SELECT DISTINCT idAlbum FROM U0.Reference,U0.Photo" +
			" WHERE U0.Reference.idPhoto = U0.Photo.idPhoto"
			+ " AND idClient=" + idClient + " )";
		ResultSet rs = st.executeQuery(requete);

		while(rs.next()) {
			listeAlbums.add(new Livre(rs.getInt("idAlbum"), rs.getString("Titre"), rs.getString("sousTitre"), 
					rs.getInt("estpublique")==1, getPhotos(rs.getInt("idAlbum")), 
					rs.getInt("idPhoto"), rs.getString("preface"), rs.getString("postface")));
		}

		return listeAlbums;
	}

	public ArrayList<Livre> getLivrePublic() throws SQLException{
		//TODO
		ArrayList<Livre> listeAlbums = new ArrayList<Livre>();

		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "SELECT * FROM U0.Album WHERE estpublique=1";
		ResultSet rs = st.executeQuery(requete);

		while(rs.next()) {
			listeAlbums.add(new Livre(rs.getInt("idAlbum"), rs.getString("Titre"), rs.getString("sousTitre"), 
					rs.getInt("estpublique")==1, getPhotos(rs.getInt("idAlbum")), 
					rs.getInt("idPhoto"), rs.getString("preface"), rs.getString("postface")));
		}

		return listeAlbums;
	}

	public void setTitre(int id, String titre) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "UPDATE U0.Album SET titre='"+titre+"' WHERE idAlbum="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public int getIDMAX() throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT MAX(idAlbum) FROM U0.Album";
		ResultSet rs = st.executeQuery(requete);
		
		rs.next();
		return rs.getInt("MAX(idAlbum)");
	}
	
	public int ajouterPhoto(Photo p, String titre, int ordre, String commentaire, int idAlbum, int idClient) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT MAX(idPhoto) FROM U0.Photo";
		ResultSet rs = st.executeQuery(requete);
		
		rs.next();
		int idPhoto = rs.getInt("MAX(idPhoto)")+1;
		
		requete = "INSERT INTO U0.Photo VALUES(" +
				idPhoto + ", '" +
				p.getChemin() + "', '" +
				p.getAppareil() + "', '" +
				p.getObjectif() + "', " +
				p.getFocale() + ", '" +
				p.getSensibilite() + "', " +
				p.getOuverture() + ", " +
				p.getVitesse_obturation() + ", " +
				p.getLongueur() + ", " +
				p.getLargeur() + ", " +
				idClient +")";
		st.executeQuery(requete);
		
		requete = "INSERT INTO U0.Reference VALUES(" +
				idAlbum + ", " +
				idPhoto + ", '" +
				titre + "', '" +
				commentaire + "', " +
				ordre + ")";
		st.executeQuery(requete);
		connexion.commit();
		return idPhoto;
	}
	
	public void majReference(int idAlbum, int idPhoto) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE Reference " +
				"SET idAlbum="+ idAlbum +
				" WHERE idAlbum=0 AND idPhoto=" + idPhoto;
		ResultSet rs = st.executeQuery(requete);
	}
	
	public int ajouterLivre(Livre l) throws SQLException{
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT MAX(idAlbum) FROM U0.Album";
		ResultSet rs = st.executeQuery(requete);
		
		rs.next();
		int idAlbum = rs.getInt("MAX(idAlbum)")+1;
		String tmp = l.isPublic_()?"1":"0";
		String tmp2 = (l.getIdPhoto() == -1)?"NULL":(l.getIdPhoto()+"");
		requete = "INSERT INTO U0.Album VALUES(" + 
				idAlbum + ", '" +
				l.getTitre() + "', '" +
				l.getSous_titre() + "', " +
				tmp + ", '" +
				l.getPreface() + "', '" +
				l.getPostface() + "', " +
				tmp2 + ")";
		
		rs = st.executeQuery(requete);
		connexion.commit();
		return idAlbum;
	}

	public void setSousTitre(int id, String sous_titre) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "UPDATE U0.Album SET sousTitre='"+sous_titre+"' WHERE idAlbum="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	public void setPublic(int id, int pub) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "UPDATE U0.Album SET estpublique='"+pub+"' WHERE idAlbum="+id;
		st.executeQuery(requete);
		connexion.commit();
	}

	public void setIdPhoto(int id, int idPhoto) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "UPDATE U0.Album SET idPhoto='"+idPhoto+"' WHERE idAlbum="+id;
		st.executeQuery(requete);
		connexion.commit();
	}

	public void setPreface(int id, String preface) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "UPDATE U0.Album SET preface='"+preface+"' WHERE idAlbum="+id;
		st.executeQuery(requete);
		connexion.commit();
	}

	public void setPostface(int id, String postface) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();

		String requete = "UPDATE U0.Album SET preface='"+postface+"' WHERE idAlbum="+id;
		st.executeQuery(requete);
		connexion.commit();
	}



}




